Site hosted by Angelfire.com: Build your free website today!

Entering Formulas

Vocabulary

Calculation Operators in Formulas

Simple Formulas

Checking and Changing Formulas

Copying Formulas Using the Fill Handle

Copying Formulas using Copy and Paste

Relative vs. Absolute Cell References

Vocabulary

Formula: an equation that performs operations on worksheet data.

Operations: mathematical operations such as addition, multiplication, division or subtraction.

Calculation Operators in Formulas

Operators: specify the type of calculation that you want to perform on the elements of a formula. (add +, subtract -, multiply *, or divide / , for example)

Arithmetic Operators: To perform basic mathematical operations such as addition, subtraction, or multiplication; combine numbers; and produce numeric results, use the following arithmetic operators.

Arithmetic
operator
Meaning
Example
+ (plus sign) Addition 3+3
– (minus sign) Subtraction
Negation		 3–1
–1
* (asterisk) Multiplication 3*3
/ (forward slash) Division 3/3
% (percent sign) Percent 20%
^ (caret) Exponentiation 3^2 (the same as 3*3)

Comparison Operators: You can compare two values with the following operators. When two values are compared by using these operators, the result is a logical value, either TRUE or FALSE.

Comparison
operator
Meaning
Example
= (equal sign) Equal to A1=B1
> (greater than sign) Greater than A1>B1
< (less than sign) Less than A1<B1
>= (greater than or equal to sign) Greater than or equal to A1>=B1
<= (less than or equal to sign) Less than or equal to A1<=B1
<> (not equal to sign) Not equal to A1<>B1

Reference Operators: Combine ranges of cells for calculations with the following operators.
Reference
operator
Meaning
Example
: (colon) Range operator, which produces one reference to all the cells between two references, including the two references B5:B15
, (comma) Union operator, which combines multiple references into one reference SUM(B5:B15,D5:D15)

Simple Formulas

If you want to perform simple calculations such as adding or subtracting the numbers in two different cells see the following example.

  1. Click the cell where you want the results of your calculation to appear.
  2. Click the formula bar and then type in an = sign.
  3. After the equals sign in the formula bar type the reference of the first cell in your calculation, B16 in this example.
  4. Type the operator for the calculation you wish to complete (+ for add, - for subtract. * for multiply, / for divide).
  5. Type the second cell reference in your calculation, B17 for this example, and then hit Enter on your keyboard.                                                          

*If you wanted to divide (/), multiply (*), or add (+) you would just use those operators instead of subtract (-).

Checking and Changing Formulas

  1. To check what formula is being used in a given cell just click on that cell and the formula will appear in the formula bar.
  2. To change the formula, double click on the cell then change the formula in the formula bar.

Copying Formulas Using the Fill Handle

If you want to use the same formula with different cell references in a new cell then follow these instructions.

  1. Click in the cell with the formula you wish to replicate.
  2. Click and hold on the fill handle and drag to the cells where you wish to copy the formula.
  3. Release the mouse button and the formula should be copied with the new cell references.  Double click on the new cell to make sure your formula is correct.

Copying Formulas using Copy and Paste

Sometimes you may want to copy formulas to cells that are not adjacent to each other.  For example,

  1. Right click on the cell with the formula you want to copy and select Copy from the drop down menu.                                            
  2. Right click on the cell you wish to copy the formula to and click on Paste Special.
  3. Click on the Formulas option in the dialogue that pops up then click O.K.
  4. Double click on the new cell to make sure the formula is correct.

Relative vs. Absolute Cell References

The above examples use examples of relative cell references.  The cell references in the formula change depending on which cell the formula is copied to.  In other words the cell references or relative to the cell in which the formula is copied. 

Sometimes we do not want the cell reference to change in other words we want an Absolute Cell Reference.  See the example below for further clarification.

  

Follow these instructions:

  1. Click on the cell in which you wish to type your formula, B18 in this example.
  2. Click in the formula bar and type your formula with $ signs before your cell references.  This makes the cell references absolute.  In this example we will type =$B$16+$B$17.
  3. Right-click on the cell with the formula you want to copy and click Copy in the drop down menu.
  4. Right-click on the the cell you want to paste the formula to and click on Paste Special.
  5. Click on the Formulas option of the pop-up dialogue.
  6. The formula, with the same cell references, should be pasted into the new cell.  Double-click on your cell to check the formula.